package com.nmc.service.dao.impl;


import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SessionFactory;

import com.nmc.common.BirthStatusEnum;
import com.nmc.service.dao.BirthDao;
import com.nmc.service.model.Birth;
import com.nmc.service.model.StatisticsModel;
import com.sun.corba.se.spi.monitoring.StatisticMonitoredAttribute;

/**
 * This class interacts with Spring's HibernateTemplate to save/delete and
 * retrieve Birth objects.
 *
*/
public class BirthDaoImpl extends GenericDaoImpl<Birth, String> implements BirthDao {

    public BirthDaoImpl(Class<Birth> persistentClass,
			SessionFactory sessionFactory) {
		super(persistentClass, sessionFactory);
		// TODO Auto-generated constructor stub
	} 

	public BirthDaoImpl(Class<Birth> persistentClass) {
		super(persistentClass);
		// TODO Auto-generated constructor stub
	}

	/**
     * Constructor that sets the entity to User.class.
     */
    public BirthDaoImpl() {
        super(Birth.class);
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    public List<Birth> getBirthList() {
        return getHibernateTemplate().find("from Birth u order by u.id");
    }

    /**
     * {@inheritDoc}
     */
    public Birth saveBirth(Birth birth) {
        if (log.isDebugEnabled()) {
            log.debug("birth's id: " + birth.getId());
        }
        getHibernateTemplate().saveOrUpdate(birth);
        // necessary to throw a DataIntegrityViolation and catch it in UserManager
        getHibernateTemplate().flush();
        return birth;
    }

    /**
     * Overridden simply to call the saveBirth method. This is happenening 
     * because saveBirth flushes the session and saveObject of BaseDaoHibernate 
     * does not.
     *
     * @param birth obj to save
     * @return the modified birth (with a primary key set if they're new)
     */
    @Override
    public Birth save(Birth birth) {
        return this.saveBirth(birth);
    }


    public List<Birth> getHospitalBirthList(int hospitalId){
    	List<Birth> birthList = null;
    	birthList = getHibernateTemplate().find("from Birth where hospital.id=?", new Long(hospitalId));
    	return birthList;
    }

    public List<Birth> getHospitalBirthListByStatus(int hospitalId,BirthStatusEnum birthStatusEnum,int count){
    	List<Birth> birthList = null;
    	String queryStr = "select * from birth where hospital_id="+hospitalId+" and status = '"+birthStatusEnum.name()+"'";
    	Query query = getSession().createSQLQuery(queryStr).addEntity(Birth.class);
    	if(count > 0){
    		query.setMaxResults(count);
    	}
    	birthList = query.list();
    	
    	return birthList;
    }
    
    public List<Birth> getZoneBirthListByStatus(int zoneId,BirthStatusEnum birthStatusEnum,int count){
    	List<Birth> birthList = null;
    	String queryStr = "SELECT * FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE z.id= "+zoneId+" and b.status = '"+birthStatusEnum.name()+"'"
				;
    	Query query = getSession().createSQLQuery(queryStr).addEntity(Birth.class);
    	if(count >0){
    		query.setMaxResults(count);
    	}
    	birthList = query.list();
    	return birthList;
    }

    public List<Birth> getZoneBirthList(Birth birth){
    	List<Birth> birthList = null;
    	int zoneId = birth.getZoneId();
    	
    	String where = "WHERE z.id= "+zoneId+" and b.status != '"+BirthStatusEnum.Draft.name()+"' ";
    	
    	if(StringUtils.isNumeric(""+birth.getHospitalId()) && birth.getHospitalId() != -1){
    		where = where +" and b.hospital_id ="+birth.getHospitalId();
    	}
    	
    	if(birth.getId() != null && StringUtils.isNotEmpty(""+birth.getId())){
    		where = where +" and b.id ="+birth.getId();
    	}
    	if(StringUtils.isNotEmpty(birth.getSearchRefId())){
    		where = where +" and b.id like '"+birth.getSearchRefId()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getSearchRegistrationId())){
    		where = where +" and b.mother_name like '"+birth.getSearchRegistrationId()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getMotherName())){
    		where = where +" and b.mother_name like '"+birth.getMotherName()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getFatherName())){
    		where = where +" and b.father_name like '"+birth.getFatherName()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getSex()) && !birth.getSex().equals("All") ){
    		where = where +" and b.sex ='"+birth.getSex()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getStatus()) && !birth.getStatus().equals("All")){
    		where = where +" and b.status ='"+birth.getStatus()+"'";
    	}
    	if(birth.getStartDate() != null && birth.getEndDate()!= null){
    		where = where + " and DATE(b.birth_date) >= '"+birth.getStartDate()+"' AND DATE(b.birth_date)<='"+birth.getEndDate()+"' ";
    	}
    	where = where +" order by b.birth_date ";
    	
    	String queryStr = "SELECT * FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id "+where; 
				;
    	birthList = getSession().createSQLQuery(queryStr).addEntity(Birth.class).list();
    	return birthList;
    }

    public List<Birth> searchBirth(Birth birth){
    	String where = " where hospital_id = "+birth.getHospitalId();
    	
    	if(StringUtils.isNotEmpty(""+birth.getId())){
    		where = where +" and id = '"+birth.getId() + "'";
    	} 
    	if(StringUtils.isNotEmpty(birth.getMotherName())){
    		where = where +" and mother_name like '"+birth.getMotherName()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getFatherName())){
    		where = where +" and father_name like '"+birth.getFatherName()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getSex()) && !birth.getSex().equals("All") ){
    		where = where +" and sex ='"+birth.getSex()+"'";
    	}
    	if(StringUtils.isNotEmpty(birth.getStatus()) && !birth.getStatus().equals("All")){
    		where = where +" and status ='"+birth.getStatus()+"'";
    	}
    	if(birth.getStartDate() != null && birth.getEndDate()!= null){
    		where = where + " and DATE(birth_date) >= '"+birth.getStartDate()+"' AND DATE(birth_date)<='"+birth.getEndDate()+"' ";
    	}
    	where = where +" order by birth_date ";
    	String queryStr = "select * from birth "+where;
    	List<Birth> birthList = null;
    	birthList = getSession().createSQLQuery(queryStr).addEntity(Birth.class).list();
    	
    	return birthList;
    }
    
    public List<Birth> searchBirthForAdminUser(Birth birth){
    	String where = " where ";
    	boolean isFirstCondition = true;
    	
    	if(birth.getHospitalId()!= null && StringUtils.isNotEmpty(""+birth.getHospitalId())) {
    		if (isFirstCondition) {
    			where = where +"hospital_id = '"+birth.getHospitalId() + "'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and hospital_id = '"+birth.getHospitalId() + "'";
    		}
    	}
    	
    	if(birth.getZoneId() != null && StringUtils.isNotEmpty(""+birth.getZoneId())) {
    		if (isFirstCondition) {
    			where = where +"zone_id = '"+birth.getZoneId() + "'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and zone_id = '"+birth.getZoneId() + "'";
    		}
    	}
    	
    	if(StringUtils.isNotEmpty(""+birth.getId())){
    		if (isFirstCondition) {
    			where = where +" id = '"+birth.getId() + "'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and id = '"+birth.getId() + "'";
    		}
    	} 
    	if(StringUtils.isNotEmpty(birth.getMotherName())){
    		if (isFirstCondition) {
    			where = where +" mother_name like '"+birth.getMotherName()+"'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and mother_name like '"+birth.getMotherName()+"'";
    		}
    	}
    	if(StringUtils.isNotEmpty(birth.getFatherName())){
    		if (isFirstCondition) {
    			where = where +" father_name like '"+birth.getFatherName()+"'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and father_name like '"+birth.getFatherName()+"'";
    		}
    	}
    	if(StringUtils.isNotEmpty(birth.getSex()) && !birth.getSex().equals("All") ){
    		if (isFirstCondition) {
    			where = where +" sex ='"+birth.getSex()+"'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and sex ='"+birth.getSex()+"'";
    		}
    	}
    	if(StringUtils.isNotEmpty(birth.getStatus()) && !birth.getStatus().equals("All")){
    		if (isFirstCondition) {
    			where = where +" status ='"+birth.getStatus()+"'";
    			isFirstCondition = false;
    		} else {
    			where = where +" and status ='"+birth.getStatus()+"'";
    		}
    	}
    	if(birth.getStartDate() != null && birth.getEndDate()!= null){
    		if (isFirstCondition) {
    			where = where + " DATE(birth_date) >= '"+birth.getStartDate()+"' AND DATE(birth_date)<='"+birth.getEndDate()+"' ";
    			isFirstCondition = false;
    		} else {
    			where = where + " and DATE(birth_date) >= '"+birth.getStartDate()+"' AND DATE(birth_date)<='"+birth.getEndDate()+"' ";
    		}
    	}
    	String queryStr = "select * from birth ";
    	if (!" where ".equalsIgnoreCase(where)){
    		queryStr = queryStr + where;
    	}
    	queryStr = queryStr + " order by birth_date ";
    	List<Birth> birthList = null;
    	birthList = getSession().createSQLQuery(queryStr).addEntity(Birth.class).list();
    	
    	return birthList;
    }


    public StatisticsModel getHospitalStatisticsCount(int hospitalId,int months)
    {
    	StatisticsModel statisticsModel = new StatisticsModel();
    	 
    	String query= "SELECT * FROM (SELECT COUNT(*) AS totalBirthCount FROM birth WHERE  hospital_id="+hospitalId+" AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS totBirthCount, " 
    				+  " (SELECT COUNT(*) AS femaleCount FROM birth WHERE  hospital_id="+hospitalId+" AND sex='Female' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS femaleCount,"
    				+"(SELECT COUNT(*) AS maleCount FROM birth WHERE  hospital_id="+hospitalId+" AND sex='Male' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS maleCount,"
    				+"(SELECT FLOOR(COUNT(*)/2) AS twinsCount FROM birth WHERE  hospital_id="+hospitalId+" AND birth_type='Twins' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS twinsCount,"
    				+"(SELECT FLOOR(COUNT(*)/3) AS tripletsCount FROM birth WHERE hospital_id="+hospitalId+" AND birth_type='Triplets' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS tripletsCount,"
    				+"(SELECT COUNT(*) AS stillBirthCount FROM birth WHERE hospital_id="+hospitalId+" AND still_birth=1 AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS stillBirthCount";
    	List statsList = null;
    	Object stats[] = (Object [])getSession().createSQLQuery(query).uniqueResult();
    	if(stats != null && stats.length >0)
    	{
    		statisticsModel.setTotalBirthCount(((BigInteger)stats[0]).intValue());
    		statisticsModel.setMaleCount(((BigInteger)stats[1]).intValue());
    		statisticsModel.setFemaleCount(((BigInteger)stats[2]).intValue());
    		statisticsModel.setTwinsCount(((BigDecimal)stats[3]).intValue());
    		statisticsModel.setTripletsCount(((BigDecimal)stats[4]).intValue());
    		statisticsModel.setStillBirthCount(((BigInteger)stats[5]).intValue());
    	}
    	return statisticsModel;
    }
    
    public StatisticsModel getZoneStatisticsCount(int zoneId,int months)
    {
    	StatisticsModel statisticsModel = new StatisticsModel();
    	 
    	String query= "SELECT * FROM (SELECT COUNT(*) AS totalBirthCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  z.id="+zoneId+" AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS totBirthCount, " 
    				+  " (SELECT COUNT(*) AS femaleCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  z.id="+zoneId+" AND sex='Female' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS femaleCount,"
    				+"(SELECT COUNT(*) AS maleCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  z.id="+zoneId+" AND sex='Male' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS maleCount,"
    				+"(SELECT FLOOR(COUNT(*)/2) AS twinsCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  z.id="+zoneId+" AND birth_type='Twins' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS twinsCount,"
    				+"(SELECT FLOOR(COUNT(*)/3) AS tripletsCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  z.id="+zoneId+" AND birth_type='Triplets' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS tripletsCount,"
    				+"(SELECT COUNT(*) AS stillBirthCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  z.id="+zoneId+" AND still_birth=1 AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS stillBirthCount," +
    				" (SELECT COUNT(*) AS zoneHospitalCount FROM hospital WHERE zone_id= "+zoneId+" ) AS zoneHospitalCount";
    	List statsList = null;
    	Object stats[] = (Object [])getSession().createSQLQuery(query).uniqueResult();
    	if(stats != null && stats.length >0)
    	{
    		statisticsModel.setTotalBirthCount(((BigInteger)stats[0]).intValue());
    		statisticsModel.setMaleCount(((BigInteger)stats[1]).intValue());
    		statisticsModel.setFemaleCount(((BigInteger)stats[2]).intValue());
    		statisticsModel.setTwinsCount(((BigDecimal)stats[3]).intValue());
    		statisticsModel.setTripletsCount(((BigDecimal)stats[4]).intValue());
    		statisticsModel.setStillBirthCount(((BigInteger)stats[5]).intValue());
    		statisticsModel.setZoneHospitalsCount(((BigInteger)stats[6]).intValue());
    	}
    	return statisticsModel;
    }
    
    public StatisticsModel getAllZoneStatisticsCount(int months)
    {
    	StatisticsModel statisticsModel = new StatisticsModel();
    	 
    	String query= "SELECT * FROM (SELECT COUNT(*) AS totalBirthCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE  birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS totBirthCount, " 
    				+  " (SELECT COUNT(*) AS femaleCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE sex='Female' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS femaleCount,"
    				+"(SELECT COUNT(*) AS maleCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE   sex='Male' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS maleCount,"
    				+"(SELECT FLOOR(COUNT(*)/2) AS twinsCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE   birth_type='Twins' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS twinsCount,"
    				+"(SELECT FLOOR(COUNT(*)/3) AS tripletsCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE   birth_type='Triplets' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS tripletsCount,"
    				+"(SELECT COUNT(*) AS stillBirthCount FROM birth b LEFT OUTER JOIN  hospital h ON b.hospital_id=h.id LEFT OUTER JOIN zones z ON h.zone_id=z.id WHERE   still_birth=1 AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS stillBirthCount," +
    				" (SELECT COUNT(*) AS zoneHospitalCount FROM hospital ) AS zoneHospitalCount";
    	List statsList = null;
    	Object stats[] = (Object [])getSession().createSQLQuery(query).uniqueResult();
    	if(stats != null && stats.length >0)
    	{
    		statisticsModel.setTotalBirthCount(((BigInteger)stats[0]).intValue());
    		statisticsModel.setMaleCount(((BigInteger)stats[1]).intValue());
    		statisticsModel.setFemaleCount(((BigInteger)stats[2]).intValue());
    		statisticsModel.setTwinsCount(((BigDecimal)stats[3]).intValue());
    		statisticsModel.setTripletsCount(((BigDecimal)stats[4]).intValue());
    		statisticsModel.setStillBirthCount(((BigInteger)stats[5]).intValue());
    		statisticsModel.setZoneHospitalsCount(((BigInteger)stats[6]).intValue());
    	}
    	return statisticsModel;
    }
    
	public int getBirthCountForHospital(int hospitalId, int year) {
		BigInteger currentCountForYear = (BigInteger)getSession().createSQLQuery("select count(*) from birth where hospital_id = " + hospitalId + " and YEAR(birth_date) = " + year).list().get(0);
		return currentCountForYear.intValue();
	}
	public void updateDeliveryDetails(String id, Birth birth){
		List<Birth> birthList = null;
		birthList = getHibernateTemplate().find("from Birth where id=?", id);
		if (birthList != null && birthList.size() > 0) {
			Birth b = birthList.get(0);
			b.setCerDeliveryDate(birth.getCerDeliveryDate());
			b.setCerDeliveryType(birth.getCerDeliveryType());
			save(b);
		}
	}
	
	public Birth getBirth(String id){
		Birth birth = null;
		
		//birth = getSession().createQuery("from Birth join comments").list().get(0);
		return birth;
	}
	
	public StatisticsModel getMaleFemaleCount(int months){
    	StatisticsModel statisticsModel = new StatisticsModel();
   	 
    	String query= "SELECT * FROM (SELECT COUNT(*) AS femaleCount FROM birth b WHERE  sex='Female' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS femaleCount, "+
    				  "  (SELECT COUNT(*) AS maleCount FROM birth b WHERE  sex='Male' AND birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01')) AS maleCount";
    	Object stats[] = (Object [])getSession().createSQLQuery(query).uniqueResult();
    	if(stats != null && stats.length >0)
    	{
    		statisticsModel.setFemaleCount(((BigInteger)stats[0]).intValue());
    		statisticsModel.setMaleCount(((BigInteger)stats[1]).intValue());
    	}
    	return statisticsModel;

	}
	
	public List getZoneMaleFemaleCountByMonths(int zoneId ,int months, String sex){
   	 
    	String query= "SELECT COUNT(*), sex, DATE_FORMAT(birth_date, '%M') AS mon FROM birth b LEFT JOIN hospital h ON b.hospital_id = h.id LEFT JOIN zones z ON h.zone_id=z.id "+
    				  " WHERE birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01') AND b.sex='"+sex+"' AND z.id="+zoneId+" GROUP BY sex,mon;";
		List stats = getSession().createSQLQuery(query).list();

		return stats;

	}
	public List getHospitalMaleFemaleCountByMonths(int hospitalId ,int months, String sex){
	   	 
    	String query= "SELECT COUNT(*), sex, DATE_FORMAT(birth_date, '%M') AS mon FROM birth b  "+
    	"WHERE birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01') AND b.sex='"+sex+"' AND b.hospital_id="+hospitalId+" GROUP BY sex,mon;";
		List stats = getSession().createSQLQuery(query).list();

		return stats;

	}
	
	public List<Object[]> getAllZonesMaleFemaleCounts(int months){
		
		String query = "SELECT id,NAME,IFNULL(birthc.mCount,0) AS maleCount,IFNULL(birthf.fCount,0) AS femaleCount FROM zones zn LEFT OUTER JOIN (SELECT COUNT(*) AS mCount, sex AS male, DATE_FORMAT(birth_date, '%M') AS mon,z.id AS zoneId FROM birth b LEFT JOIN hospital h ON b.hospital_id = h.id LEFT JOIN zones z ON h.zone_id=z.id "+ 
			 " WHERE birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01') AND b.sex='Male' GROUP BY zoneId ) AS birthc ON zn.id = birthc.zoneId "+
			" LEFT OUTER JOIN (SELECT COUNT(*) AS fCount, sex AS female, DATE_FORMAT(birth_date, '%M') AS mon,z.id AS zoneId FROM birth b LEFT JOIN hospital h ON b.hospital_id = h.id LEFT JOIN zones z ON h.zone_id=z.id "+ 
				"	WHERE birth_date >=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -"+(months-1)+" MONTH), '%Y-%m-01') AND b.sex='Female' GROUP BY zoneId ) AS birthf ON zn.id = birthf.zoneId 	";
		List<Object[]> objList = getSession().createSQLQuery(query).list();
		return objList;
		
	}
}
